package com.laxcen.smoke.dao;

import com.laxcen.smoke.entity.AbNormalRecord;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;
import org.springframework.stereotype.Repository;

import java.util.Date;
import java.util.List;

@Mapper
@Repository
public interface AbNormalDao {

    @Insert("insert into abnormalrecord(id,smartno,smartboxno,abnormaltype,createtime,state) values(#{id},#{smartno},#{sboxno},#{abnormaltype},#{createtime},#{state})")
    int insert(String id, String smartno, String sboxno, int abnormaltype, Date createtime, int state);

    //    //添加操作订单详情表
    @Insert("insert into operaterecorddetailtable(id,recordid,smartno,boxno,optime,optype,createtime) values(#{id},#{recordid},#{smartno},#{boxno},#{optime},#{optype},#{createtime}) ")
    int insertuporderdetail(String id, String recordid, String smartno, String boxno, Date optime, int optype, Date createtime);

    //添加操作订单表
    @Insert("insert into operaterecord(id,smartno,boxno,opid,optime,optype,orderno,createtime) values(#{id},#{smartno},#{boxno},#{opid},#{optime},#{optype},#{orderno},#{createtime}) ")
    int insertuporder(String id, String smartno, String boxno, String opid, Date optime, int optype, String orderno, Date createtime);

    @Update("update abnormalrecord set state = #{state},content = #{content},updatetime = #{updatetime} where id = #{id}")
    int update(String id, int state, String content, Date updatetime);

    @Update("update smartbox set status = #{status} where id = #{id}")
    int updateBoxStatus(int status, String id);

    @Update("update smart set status = #{status} where no = #{smartno}")
    int updateSmartByNo(int status, String smartno);

    @Update("update smartboard set status = #{status} where id = #{id}")
    int updatesmartBoardBy(int status, String smartno, String id);

    @Select("select sb.id from smartbox sb right join real_board_box rbb on sb.id = rbb.BoxId right join real_smart_smartboard rss on rbb.BoardId = rss.BoardId right join smart sm on rss.SmartId = sm.id where sm.no = #{smartno} and sb.no = #{boxno}")
    List<String> getsmartboxid(String smartno, String boxno);

    //获取指定智能柜编号下的主柜id
    @Select("select sbd.id from smartboard sbd right join real_smart_smartboard rss on sbd.id = rss.boardid right join smart sm on  sm.id = rss.smartid where  sm.no = #{smartno} and sbd.specification = 1")
    String getsmartboardid(String smartno);

    @Update("update smartboard set status =  1 where id = #{id}")
    int updatesmartboard(String id);

    @Select("select sbd.id from smartboard sbd right join real_smart_smartboard rss on sbd.id = rss.BoardId left join smart sm on rss.SmartId = sm.id where sm.no = #{smartno}")
    List<String> getIdBySmartno(String smartno);

    @Select("select * from abnormalrecord where id = #{id}")
    AbNormalRecord getAbnById(String id);

    @Select("select * from abnormalrecord where smartno = #{smartno} and state = #{status}")
    List<AbNormalRecord> getNotState(String smartno, int status);

    @Select("select distinct abd.* from abnormalrecord abd right join smart sm on abd.smartno = sm.no right join real_smart_smartboard rss on sm.id = rss.smartid where abd.smartno = #{smartno} order by abd.createtime asc")
    List<AbNormalRecord> getListByNo(String smartno);

    @Select("select distinct abd.* from abnormalrecord abd left join smart sm on abd.smartno = sm.no right join real_smart_smartboard rss on sm.id = rss.smartid where abd.smartno = #{smartno} and abd.state = 1 and  sm.no in (select distinct sm.no from smart sm right join real_smart_smartboard rss on sm.id = rss.SmartId where sm.address like CONCAT('%',#{place},'%')) ")
    List<AbNormalRecord> getListByNoRole(String smartno, String place);

    @Select("select distinct abd.* from abnormalrecord abd right join smart sm on abd.smartno = sm.no right join real_smart_smartboard rss on sm.id = rss.smartid where abd.createtime between #{starttime} and #{endtime} order by abd.createtime desc")
    List<AbNormalRecord> getListByTime(Date starttime, Date endtime);

    @Select("select distinct abd.* from abnormalrecord abd left join smart sm on abd.smartno = sm.no right join real_smart_smartboard rss on sm.id = rss.smartid  where abd.createtime between #{starttime} and #{endtime}  and abd.state = 1 and  sm.no in (select distinct sm.no from smart sm right join real_smart_smartboard rss on sm.id = rss.SmartId where sm.address like CONCAT('%',#{place},'%')) order by abd.state desc,abd.createtime desc")
    List<AbNormalRecord> getListByTimeRole(Date starttime, Date endtime, String place);

    @Select("select distinct abd.* from abnormalrecord abd right join smart sm on abd.smartno = sm.no right join real_smart_smartboard rss on sm.id = rss.smartid where abd.smartno = #{smartno} and abd.createtime between #{starttime} and #{endtime} order by abd.createtime desc")
    List<AbNormalRecord> getListByTimeAndNo(String smartno, Date starttime, Date endtime);

    @Select("select distinct abd.* from abnormalrecord abd left join smart sm on abd.smartno = sm.no  right join real_smart_smartboard rss on sm.id = rss.smartid  where abd.createtime between #{starttime} and #{endtime} and abd.smartno = #{smartno}  and abd.state = 1 and  sm.no in (select distinct sm.no from smart sm right join real_smart_smartboard rss on sm.id = rss.SmartId where sm.address like CONCAT('%',#{place},'%')) order by abd.createtime desc")
    List<AbNormalRecord> getListByTimeandNoRole(String smartno, Date starttime, Date endtime, String place);

    @Select("select distinct abd.* from abnormalrecord abd right join smart sm on abd.smartno = sm.no right join real_smart_smartboard rss on sm.id = rss.smartid order by abd.createtime desc ")
    List<AbNormalRecord> getListAll();

    @Select("select distinct abd.* from abnormalrecord abd left join smart sm on abd.smartno = sm.no right join real_smart_smartboard rss on sm.id = rss.smartid  where sm.no in (select distinct sm.no from smart sm right join real_smart_smartboard rss on sm.id = rss.SmartId where sm.address like CONCAT('%',#{place},'%')) order by abd.createtime desc ")
    List<AbNormalRecord> getListAllRole(String place);

    @Select("select abd.* from abnormalrecord abd right join smart sm on abd.smartno = sm.no right join real_smart_smartboard rss on sm.id = rss.smartid where abd.state = #{state} order by abd.createtime desc ")
    List<AbNormalRecord> getListAllByState(int state);

    @Select("select abd.* from abnormalrecord abd left join smart sm on abd.smartno = sm.no  right join real_smart_smartboard rss on sm.id = rss.smartid  where abd.state = #{state} and sm.no in (select distinct sm.no from smart sm right join real_smart_smartboard rss on sm.id = rss.SmartId where sm.address like CONCAT('%',#{place},'%'))  order by abd.createtime desc ")
    List<AbNormalRecord> getListAllByStateRole(int state, String place);

    @Select("select count(*) from abnormalrecord where state = 1")
    int getCount();

    @Select("select count(*) from abnormalrecord abd left join smart sm on abd.smartno = sm.no  where sm.no in (select distinct sm.no from smart sm right join real_smart_smartboard rss on sm.id = rss.SmartId left join abnormalrecord abd on sm.no = abd.smartno where sm.address like CONCAT('%',#{place},'%') and  abd.state = 1)")
    int getCountRole(String place);

}
